﻿using System.ComponentModel;
using System.Data.OleDb;
using Entity;

namespace DAO
{
    public class NhomTinDAO
    {
        static string connectionString = new connection().getConnectionString();


        public BindingList<NhomTin> LoadAll()
        {

            BindingList<NhomTin> list = new BindingList<NhomTin>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from NhomTin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                int ma = (int)reader["MaNhomTin"];
                string ten = (string)reader["TenNhomTin"];
                NhomTin kv = new NhomTin();
                kv.MaNhomTin = ma;
                kv.TenNhomTin = ten;
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }

        public NhomTin LoadByID(int id)
        {

            BindingList<NhomTin> list = new BindingList<NhomTin>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from NhomTin WHERE MaNhomTin = @MaNhomTin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaNhomTin", OleDbType.VarChar).Value = id;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                int ma = (int)reader["MaNhomTin"];
                string ten = (string)reader["TenNhomTin"];
                NhomTin kv = new NhomTin();
                kv.MaNhomTin = ma;
                kv.TenNhomTin = ten;
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list[0];
        }
        public int DeleteByStringId(int id)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "DELETE FROM NhomTin WHERE MaNhomTin = @MaNhomTin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaNhomTin", OleDbType.VarChar).Value = id;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public int Insert(NhomTin kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "INSERT INTO NhomTin (TenNhomTin) VALUES(@TenNhomTin);";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TenNhomTin", OleDbType.VarChar).Value = kv.TenNhomTin;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
        public int Update(NhomTin kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "UPDATE NhomTin SET TenNhomTin = @TenNhomTin WHERE MaNhomTin = @MaNhomTin;";

            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TenNhomTin", OleDbType.VarChar).Value = kv.TenNhomTin;
            command.Parameters.Add("@MaNhomTin", OleDbType.VarChar).Value = kv.MaNhomTin;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
    }
}
